﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Document_Management
{
    public partial class frmStockMnt : Form
    {

        int width, height;
        int iflag = 0;// co nay xac sinh hanh dong cua nut btninsertupdatedelete
        string 材編, role;
        bool b庫存Search, b庫存Insert, b庫存Edit, b庫存Del;
        static string connectionString = ConfigurationManager.ConnectionStrings["庫存管理"].ConnectionString;
        PublicClass x = new PublicClass();

        public frmStockMnt(bool b庫存Search,bool b庫存Insert, bool b庫存Edit,bool b庫存Del)
        {
            InitializeComponent();
            this.b庫存Search = b庫存Search;
            this.b庫存Insert = b庫存Insert;
            this.b庫存Edit = b庫存Edit;
            this.b庫存Del = b庫存Del;
        }

        private void frmStockMnt_Load(object sender, EventArgs e)
        {

            LoadAll();
            LoadAll_管理者();
            LoadAll_供應商();
            loadall_替代用料();

        }
        public void LoadAll()
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                //if (role != "管理")
                //{
                //    btnaddproduct.Visible = false;
                //    btnEditproduct.Visible = false;
                //    btndelete.Visible = false;
                //}
                con.Open();

                //"SELECT pocet FROM klisluz " + 
                //                                "WHERE id = @id",spojeni);
                SqlDataAdapter da = new SqlDataAdapter("sp_庫存_尋找", con);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataTable dt = new DataTable();
                da.Fill(dt);
                dataGridview庫存.DataSource = dt;

                PublicClass.setRowNumber(dataGridview庫存);

                // dataGridview庫存.Columns[0].Width = 30;
                //dataGridview庫存.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                //dataGridview庫存.Columns[0].SortMode = DataGridViewColumnSortMode.NotSortable;
                //dataGridview庫存.Columns[3].HeaderText = "現有庫" + "\n存量";
                //dataGridview庫存.Columns[3].Width = 70;
                dataGridview庫存.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dataGridview庫存.Columns[4].Width = 70;
                dataGridview庫存.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

                width = this.Size.Width;
                height = this.Size.Height;
                gBAddNew.Visible = false;
                this.Size = new System.Drawing.Size(this.Size.Width, this.Size.Height - gBAddNew.Height);
                this.DesktopLocation = new Point(0, 0);
                //
                //dataGridview庫存.FirstDisplayedScrollingRowIndex = dataGridview庫存.SelectedRows[0].Index;


                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }


        }
        public void LoadPart()
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {

                con.Open();

                //"SELECT pocet FROM klisluz " + 
                //                                  "WHERE id = @id",spojeni);
                SqlDataAdapter da = new SqlDataAdapter("sp_庫存_尋找", con);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataTable dt = new DataTable();
                //dt.Columns.Add("項次");//add new 項次 column
                da.Fill(dt);
                dataGridview庫存.DataSource = dt;
                //for (int i = 0; i < dt.Rows.Count; i++) dt.Rows[i][0] = (i + 1).ToString();
                //change column width
                PublicClass.setRowNumber(dataGridview庫存);
                //dataGridview庫存.Columns[0].Width = 30;
                //dataGridview庫存.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dataGridview庫存.Columns[3].HeaderText = "現有庫" + "\n存量";
                dataGridview庫存.Columns[3].Width = 70;
                dataGridview庫存.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dataGridview庫存.Columns[4].Width = 70;
                dataGridview庫存.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }

        private void btnaddproduct_Click(object sender, EventArgs e)
        {
            //btnaddproduct.Enabled = false;
            if (!b庫存Insert) btnaddproduct.Enabled = false;
            btn_檢查.Visible = true;
            txt數量.Visible = true;
            lb數量.Visible = true;
            try
            {
                iflag = 1;
                btninsertupdatedelete.Text = "入庫";////" + "N'" + this.txtsophongktx.Text.ToString() + "'"
                this.Size = new System.Drawing.Size(width, height);
               // gBAddNew.ResetText();
                gBAddNew.ResetText();
                txt品名.ResetText();
                txt材編.ResetText();
                txt規格.ResetText();
                txt單位數量.ResetText();
                txt安全庫存量.ResetText();
                txt供應商碼.ResetText();
                txt數量.ResetText();
                rtb備註.ResetText();
                txt管理者代號.ResetText(); 
                產品照片.Image = null;

                gBAddNew.Visible = true;
                txt加附.Enabled = true;
                txt材編.Enabled = true;
                txt規格.Enabled = true;
                txt品名.Enabled = true;
                txt單位數量.Enabled = true;
                //txtreplaceproductcode.Enabled = true;
                txt安全庫存量.Enabled = true;
                // txt現有庫存量.Enabled = true;
                //txtsupplier.Enabled = true;
                rtb備註.Enabled = true;
                chb停止供貨.Enabled = true;
                chb停止供貨.Checked = false;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }


        }

        private void btnOutStock_Click(object sender, EventArgs e)
        {

            if (!b庫存Del) btnOutStock.Enabled = false;
           

            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                iflag = 3;
                btn_檢查.Visible = false;
                txt數量.Visible = true;
                txt數量.ResetText();
                btninsertupdatedelete.Text = "出入";
                this.Size = new System.Drawing.Size(width, height);
                gBAddNew.Visible = true;

                產品照片.Visible = true; //Image.FromFile(@"Document Management\Resources\car4.gif");
                gBAddNew.Visible = true;
                txt加附.Enabled = false;
                txt材編.Enabled = false;
                txt規格.Enabled = false;
                txt品名.Enabled = false;
                txt單位數量.Enabled = false;

                txt安全庫存量.Enabled = false;

                rtb備註.Enabled = false;
                chb停止供貨.Enabled = false;
                lb數量.Visible = true;
                int r = dataGridview庫存.CurrentCell.RowIndex;//get current cell index
                材編 = dataGridview庫存.Rows[r].Cells[0].Value.ToString();//primary key
                string strcmd = "select * from 產品,管理者,供應商 where 材編=" + "N'" + 材編 + "' and 產品.供應商碼=供應商.供應商碼 and 管理者.管理者代號=產品.管理者代號";
                SqlDataAdapter da2 = new SqlDataAdapter(strcmd, con);
                DataTable dt2 = new DataTable();
                da2.Fill(dt2);
                txt材編.Text = 材編;
                txt品名.Text = dt2.Rows[0]["品名"].ToString();
                txt單位數量.Text = dt2.Rows[0]["單位數量"].ToString();
                txt規格.Text = dt2.Rows[0]["規格"].ToString();
                txt安全庫存量.Text = dt2.Rows[0]["安全庫存量"].ToString();
                chb停止供貨.Checked = (bool)dt2.Rows[0]["停止供貨"];
                rtb備註.Text = dt2.Rows[0]["備註"].ToString();
                txt供應商碼.Text = dt2.Rows[0]["供應商名稱"].ToString();

                if (string.IsNullOrEmpty(dt2.Rows[0]["照片"].ToString())) 產品照片.Image = null;
                else 產品照片.Image = byteArraytoImage((byte[])dt2.Rows[0]["照片"]);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnEditproduct_Click(object sender, EventArgs e)
        {


            if (!b庫存Edit) btnEditproduct.Enabled = false;
           
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                //FrmChangeProducts frm = new FrmChangeProducts();
                //frm.ShowDialog();
                iflag = 2;
                btn_檢查.Visible = false;
                btninsertupdatedelete.Text = "更新";
                this.Size = new System.Drawing.Size(width, height);
                txt數量.Visible = false;
                gBAddNew.Visible = true;

                txt品名.Enabled = true;

                txt材編.Enabled = true;

                txt規格.Enabled = true;

                txt單位數量.Enabled = true;

                txt安全庫存量.Enabled = true;
                lb數量.Visible = false;
                rtb備註.Enabled = true;
                chb停止供貨.Enabled = true;
                產品照片.Visible = true;
                rtb備註.Enabled = true;
                chb停止供貨.Enabled = true;

                txt加附.Enabled = true;

                int r = dataGridview庫存.CurrentCell.RowIndex;//get current cell index
                材編 = dataGridview庫存.Rows[r].Cells[0].Value.ToString();//primary key
                string strcmd = "select * from 產品,管理者,供應商 where 材編=" + "N'" + 材編 + "' and 產品.供應商碼=供應商.供應商碼 and 管理者.管理者代號=產品.管理者代號";
                SqlDataAdapter da2 = new SqlDataAdapter(strcmd, con);
                DataTable dt2 = new DataTable();
                da2.Fill(dt2);
                txt材編.Text = 材編;
                txt品名.Text = dt2.Rows[0]["品名"].ToString();
                txt單位數量.Text = dt2.Rows[0]["單位數量"].ToString();
                txt規格.Text = dt2.Rows[0]["規格"].ToString();
                txt安全庫存量.Text = dt2.Rows[0]["安全庫存量"].ToString();
                chb停止供貨.Checked = (bool)dt2.Rows[0]["停止供貨"];
                rtb備註.Text = dt2.Rows[0]["備註"].ToString();
                txt供應商碼.Text = dt2.Rows[0]["供應商碼"].ToString();
                txt管理者代號.Text = dt2.Rows[0]["管理者代號"].ToString();

                if (string.IsNullOrEmpty(dt2.Rows[0]["照片"].ToString())) 產品照片.Image = null;// byteArraytoImage((byte[])dt2.Rows[0]["產品照片"]);//wrong
                else 產品照片.Image = byteArraytoImage((byte[])dt2.Rows[0]["照片"]);
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }



        private void btnCancels_Click(object sender, EventArgs e)
        {
            gBAddNew.Visible = false;
            this.Size = new Size(width, height - gBAddNew.Height);
            txt加附.ResetText();
            txt品名.ResetText();
            txt單位數量.ResetText();
            txt安全庫存量.ResetText();
            txt數量.ResetText();
            txt材編.ResetText();
            txt管理者代號.ResetText();
            txt供應商碼.ResetText();

            rtb備註.ResetText();
            chb停止供貨.Checked = false;
        }

        private void btninsertupdatedelete_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {

                if (iflag == 1)//insert
                {

                    //tien hanh insert
                    if (txt材編.Text == "")
                    {
                        MessageBox.Show("請您輸入材編", "錯誤", MessageBoxButtons.OK,MessageBoxIcon.Error);
                        txt材編.Focus();
                        return;
                    }

                    if (txt品名.Text == "")
                    {
                        MessageBox.Show("請您輸入品名", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        txt品名.Focus();
                        return;
                    }
                    int number1;
                    bool x = int.TryParse(txt單位數量.Text, out number1);
                    if (!x)
                    {
                        MessageBox.Show("請您輸入單位數量(int)", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        txt單位數量.Focus();
                        return;
                    }
                    int number2;
                    bool y = int.TryParse(txt安全庫存量.Text, out number2);
                    if (!y)
                    {
                        MessageBox.Show("請您輸入安全庫存量(int)", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        txt安全庫存量.Focus();
                        return;
                    }
                    int number3;
                    bool z = int.TryParse(txt數量.Text, out number3);
                    if (!z)
                    {
                        MessageBox.Show("請您輸入數量(int)", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        txt數量.Focus();
                        return;
                    }

                    if (txt供應商碼.Text == "")
                    {
                        MessageBox.Show("請您輸入供應商", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        txt供應商碼.Focus();
                        return;
                    }
                    DialogResult answer = MessageBox.Show("您確定要新增" + int.Parse(txt數量.Text) + "產品", "警報", MessageBoxButtons.YesNo,MessageBoxIcon.Question);
                    if (answer == DialogResult.Yes)
                    {
                        //bat dau insert vao database
                        SqlCommand cmd_count = new SqlCommand();
                        cmd_count.CommandText = "sp_產品_count";
                        cmd_count.CommandType = CommandType.StoredProcedure;
                        cmd_count.Parameters.AddWithValue("@材編", txt材編.Text);
                        cmd_count.Connection = con;
                        con.Open();
                        var icount = cmd_count.ExecuteScalar();
                        if (int.Parse(icount.ToString()) == 0)
                        {
                            string sp_insert = "sp_產品_新增";
                            SqlCommand cmd = new SqlCommand(sp_insert, con);
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@材編", txt材編.Text);
                            cmd.Parameters.AddWithValue("@品名", txt品名.Text);
                            cmd.Parameters.AddWithValue("@規格", txt規格.Text);
                            cmd.Parameters.AddWithValue("@供應商碼", txt供應商碼.Text);
                            cmd.Parameters.AddWithValue("@單位數量", int.Parse(txt單位數量.Text));
                            cmd.Parameters.AddWithValue("@停止供貨", chb停止供貨.Checked);
                            cmd.Parameters.AddWithValue("@管理者代號", txt管理者代號.Text);

                            if (txt加附.Text.ToString() != "")
                            {
                                byte[] imageData = ReadFile(txt加附.Text.ToString());//ReadFile tu viet o ben duoi
                                cmd.Parameters.AddWithValue("@照片", (object)imageData);
                            }
                            cmd.Parameters.AddWithValue("@安全庫存量", int.Parse(txt安全庫存量.Text));
                            //現有庫存量
                            cmd.Parameters.AddWithValue("@現有庫存量", int.Parse(txt數量.Text));
                            cmd.Parameters.AddWithValue("@備註", rtb備註.Text.ToString());
                            cmd.ExecuteNonQuery();

                            string sp_新增_替代用料 = "sp_替代用料_新增";
                            SqlCommand cmd0 = new SqlCommand(sp_新增_替代用料, con);
                            cmd0.CommandType = CommandType.StoredProcedure;
                            cmd0.Parameters.AddWithValue("@替代用料碼", string.Empty);
                            cmd0.Parameters.AddWithValue("@替代用料名稱", string.Empty);
                            cmd0.Parameters.AddWithValue("@材編", txt材編.Text);
                            cmd0.ExecuteNonQuery();
                            MessageBox.Show("新增成功");
                            Searchby();
                            //reset all text
                            txt材編.ResetText();
                            txt品名.ResetText();
                            txt單位數量.ResetText();
                            txt安全庫存量.ResetText();
                            txt管理者代號.ResetText();
                            txt供應商碼.ResetText();
                            //cmb供應商.ResetText();
                            //cmb管理者.ResetText();
                            chb停止供貨.Checked = false;
                            rtb備註.ResetText();
                            txt規格.ResetText();
                            txt數量.ResetText();
                            this.Size = new System.Drawing.Size(this.Size.Width, this.Size.Height - gBAddNew.Height);
                        }
                        else
                        {
                            //automation fill other filed
                            string sp_update = "sp_產品_更新_+n";
                            SqlCommand cmd1 = new SqlCommand(sp_update, con);
                            cmd1.CommandType = CommandType.StoredProcedure;
                            cmd1.Parameters.AddWithValue("@材編", txt材編.Text);
                            cmd1.Parameters.AddWithValue("@n", txt數量.Text);
                            cmd1.ExecuteNonQuery();
                            MessageBox.Show("更新成功");
                            Searchby();
                            txt材編.ResetText();
                            txt品名.ResetText();
                            txt單位數量.ResetText();
                            txt安全庫存量.ResetText();
                            txt管理者代號.ResetText();
                            txt供應商碼.ResetText();
                            chb停止供貨.Checked = false;
                            rtb備註.ResetText();
                            txt加附.ResetText();
                            txt數量.ResetText();
                            this.Size = new System.Drawing.Size(this.Size.Width, this.Size.Height - gBAddNew.Height);
                        }
                    }
                    else return;

                    con.Close();
                    return;

                }
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
                return;
            }

            if (iflag == 2)
            {
                //
                try
                {
                    if (txt材編.Text == "")
                    {
                        MessageBox.Show("請您輸入材編", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        txt材編.Focus();
                        return;
                    }

                    if (txt品名.Text == "")
                    {
                        MessageBox.Show("請您輸入品名", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        txt品名.Focus();
                        return;
                    }
                    int number1;
                    bool x = int.TryParse(txt單位數量.Text, out number1);
                    if (!x)
                    {
                        MessageBox.Show("請您輸入單位數量(int)", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        txt單位數量.Focus();
                        return;
                    }
                    int number2;
                    bool y = int.TryParse(txt安全庫存量.Text, out number2);
                    if (!y)
                    {
                        MessageBox.Show("請您輸入安全庫存量(int)", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        txt安全庫存量.Focus();
                        return;
                    }
                    //int number3;
                    //bool z = int.TryParse(txt數量.Text, out number3);
                    //if (!z)
                    //{
                    //    MessageBox.Show("請您輸入數量(int)", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    //    txt數量.Focus();
                    //    return;
                    //}

                    if (txt供應商碼.Text == "")
                    {
                        MessageBox.Show("請您輸入供應商", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        txt供應商碼.Focus();
                        return;
                    }


                    lb數量.Visible = false;
                    txt數量.Visible = false;
                    string sp_update = "sp_產品_更新";
                    SqlCommand cmd3 = new SqlCommand(sp_update, con);
                    cmd3.CommandType = CommandType.StoredProcedure;
                    con.Open();
                    cmd3.Parameters.AddWithValue("@材編1", 材編);
                    cmd3.Parameters.AddWithValue("@材編2", txt材編.Text);
                    cmd3.Parameters.AddWithValue("@品名", txt品名.Text);
                    cmd3.Parameters.AddWithValue("@規格", txt規格.Text);
                    cmd3.Parameters.AddWithValue("@供應商碼", txt供應商碼.Text);
                    cmd3.Parameters.AddWithValue("@單位數量", int.Parse(txt單位數量.Text));
                    cmd3.Parameters.AddWithValue("@停止供貨", chb停止供貨.Checked);
                    cmd3.Parameters.AddWithValue("@管理者代號", txt管理者代號.Text);
                    if (txt加附.Text != "")
                    {
                        byte[] imageData = ReadFile(txt加附.Text.ToString());//ReadFile tu viet o ben duoi
                        cmd3.Parameters.AddWithValue("@照片", (object)imageData);
                    }

                    else
                    {
                        string cmdsqlimage = string.Format("select 照片 from 產品 where 材編='{0}'", 材編);
                        SqlCommand command = new SqlCommand(cmdsqlimage, con);
                        byte[] reader;
                        if (string.IsNullOrEmpty(command.ExecuteScalar().ToString()))
                            cmd3.Parameters.AddWithValue("@照片", null);
                        else
                        {
                            reader = (byte[])command.ExecuteScalar();

                            cmd3.Parameters.AddWithValue("@照片", (object)reader);
                        }
                    }


                    cmd3.Parameters.AddWithValue("@安全庫存量", int.Parse(txt安全庫存量.Text));
                    //現有庫存量
                    //cmd3.Parameters.AddWithValue("@現有庫存量", 1);
                    cmd3.Parameters.AddWithValue("@備註", rtb備註.Text.ToString());
                    cmd3.ExecuteNonQuery();
                    //update 替代用料碼
                    string strcmd22 = String.Format("update 替代用料 set 替代用料碼=N'{0}',替代用料名稱=N'{1}' where 替代用料碼=N'{2}'", txt材編.Text, txt品名.Text, 材編);
                    SqlCommand cmd22 = new SqlCommand(strcmd22, con);
                    cmd22.ExecuteNonQuery();
                    MessageBox.Show("更新成功");
                    Searchby();
                    loadall_替代用料();
                    con.Close();
                    this.Size = new System.Drawing.Size(this.Size.Width, this.Size.Height - gBAddNew.Height);
                    return;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    Close();
                    return;
                }

            }
            if (iflag == 3)
            {
                //iflag=3, out of stock
                //檢查 現有量庫存=1，進行刪除，》1，更新 現有=現有-1

                try
                {
                    SqlCommand sqlcmd = new SqlCommand();
                    sqlcmd.Connection = con;
                    con.Open();
                    sqlcmd.CommandText = "select 現有庫存量 from 產品 where 材編=" + "N'" + 材編 + "'";
                    sqlcmd.CommandType = CommandType.Text;
                    var icount = sqlcmd.ExecuteScalar();
                    int inumdel;
                    bool k = int.TryParse(txt數量.Text, out inumdel);
                    int istocknumber = int.Parse(icount.ToString());
                    if (!k || inumdel > int.Parse(icount.ToString())||inumdel==0)
                    {
                        MessageBox.Show("請您輸入產品數量要刪除(int,<" + istocknumber + ")", "錯誤", MessageBoxButtons.OK);
                        txt數量.Focus();
                        return;
                    }
                    if (inumdel == istocknumber)//delete from database
                    {

                        DialogResult answer = MessageBox.Show("您確定要出入" + inumdel + "產品碼", "警報", MessageBoxButtons.YesNo);
                        if (answer == DialogResult.Yes)
                        {
                            string sp_delete = "sp_產品_刪除";
                            SqlCommand sqldelete = new SqlCommand();
                            sqldelete.Connection = con;
                            sqldelete.CommandType = CommandType.StoredProcedure;
                            sqldelete.CommandText = sp_delete;
                            sqldelete.Parameters.AddWithValue("@材編", 材編);
                            MessageBox.Show("刪除成功");
                            sqldelete.ExecuteNonQuery();
                            Searchby();
                            this.Size = new System.Drawing.Size(this.Size.Width, this.Size.Height - gBAddNew.Height);
                            return;
                        }
                        else
                        {
                            //LoadAll();
                            return;
                        }

                    }
                    else
                    {
                        DialogResult answer = MessageBox.Show("您確定要出入" + int.Parse(txt數量.Text) + "產品嗎？", "警報", MessageBoxButtons.YesNo,MessageBoxIcon.Question);
                        if (answer == DialogResult.Yes)
                        {
                            string sp_increment = "sp_產品_更新_-n";
                            SqlCommand sqlincrement = new SqlCommand();
                            sqlincrement.Connection = con;
                            sqlincrement.CommandType = CommandType.StoredProcedure;
                            sqlincrement.CommandText = sp_increment;
                            sqlincrement.Parameters.AddWithValue("@材編", 材編);
                            sqlincrement.Parameters.AddWithValue("@n", int.Parse(txt數量.Text));
                            sqlincrement.ExecuteNonQuery();
                            MessageBox.Show("出入成功");
                            Searchby();
                            this.Size = new System.Drawing.Size(this.Size.Width, this.Size.Height - gBAddNew.Height);
                            return;
                        }
                        else
                        {
                            LoadAll();
                            return;
                        }


                    }

                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show(ex.ToString());
                    return;
                }
                finally { con.Close(); }
            }
        }

        private void btn_加附_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog dialog = new OpenFileDialog();

                dialog.Title = "開啟照片";
                dialog.Filter = "照片當 (*.jpg, *.jpeg, *.jpe, *.bmp, *.png) | *.jpg; *.jpeg; *.jpe; *.bmp; *.png"; ;//Bitmap, Icon,GIF,bmp,ico,gif,jpeg
                var result = dialog.ShowDialog();
                if (result == DialogResult.OK)
                {
                    txt加附.Text = dialog.FileName;
                    Image img = Image.FromFile(txt加附.Text.ToString());
                    產品照片.Image = img;

                }
                else return;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }

        }
        public Image byteArraytoImage(byte[] filesbyte)
        {
            using (System.IO.MemoryStream filestream = new System.IO.MemoryStream(filesbyte))
            {
                return Image.FromStream(filestream);
            }
        }
        byte[] ReadFile(string sPath)
        {
            //Initialize byte array with a null value initially.
            byte[] data = null;

            //Use FileInfo object to get file size.
            FileInfo fInfo = new FileInfo(sPath);
            long numBytes = fInfo.Length;

            //Open FileStream to read file
            FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

            //Use BinaryReader to read file stream into byte array.
            BinaryReader br = new BinaryReader(fStream);

            //When you use BinaryReader, you need to supply number of bytes 
            //to read from file.
            //In this case we want to read entire file. 
            //So supplying total number of bytes.
            data = br.ReadBytes((int)numBytes);

            return data;
        }

        private void txt材編_TextChanged(object sender, EventArgs e)
        {

        }

        private void btn_檢查_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection con = new SqlConnection(connectionString);
                string strcmd = "select * from 產品,管理者,供應商 where 材編=" + "N'" + txt材編.Text + "' and 產品.供應商碼=供應商.供應商碼 and 管理者.管理者代號=產品.管理者代號";
                SqlDataAdapter da1 = new SqlDataAdapter(strcmd, con);
                DataTable dt1 = new DataTable();
                da1.Fill(dt1);
                //get data from datatable
                if (dt1.Rows.Count == 0)
                {
                    MessageBox.Show("這是新產品，請輸入其他訊息","訊息",MessageBoxButtons.OK,MessageBoxIcon.Information);
                    txt品名.Focus();
                }
                else
                {
                    txt品名.Text = dt1.Rows[0]["品名"].ToString();
                    txt單位數量.Text = dt1.Rows[0]["單位數量"].ToString();
                    txt規格.Text = dt1.Rows[0]["規格"].ToString();
                    txt安全庫存量.Text = dt1.Rows[0]["安全庫存量"].ToString();
                    chb停止供貨.Checked = (bool)dt1.Rows[0]["停止供貨"];
                    rtb備註.Text = dt1.Rows[0]["備註"].ToString();
                    txt供應商碼.Text = dt1.Rows[0]["供應商碼"].ToString();
                    txt管理者代號.Text = dt1.Rows[0]["管理者代號"].ToString();

                    產品照片.Image = (string.IsNullOrEmpty(txt加附.Text) ? null : byteArraytoImage((byte[])dt1.Rows[0]["照片"]));

                }
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }

        private void btn查詢_Click(object sender, EventArgs e)
        {

            if (!b庫存Search) btn查詢.Enabled = false;
      
            Searchby();

        }
        public void Searchby()
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {

                con.Open();

                string sp = "sp_庫存_尋找";
                SqlCommand cmd = new SqlCommand(sp, con);
                cmd.CommandType = CommandType.StoredProcedure;
                if (txt品名S.Text != "") cmd.Parameters.AddWithValue("@品名", txt品名S.Text);
                if (txt材編S.Text != "") cmd.Parameters.AddWithValue("@材編", txt材編S.Text);
                if (txt規格S.Text != "") cmd.Parameters.AddWithValue("@規格", txt規格S.Text);
                if (txt供應商S.Text != "") cmd.Parameters.AddWithValue("@供應商名稱", txt供應商S.Text);
                if (txt現有庫存量S.Text != "") cmd.Parameters.AddWithValue("@現有庫存量", txt現有庫存量S.Text);
                if (txt安全庫存量S.Text != "") cmd.Parameters.AddWithValue("@安全庫存量", txt安全庫存量S.Text);
                if (txt替代用料S.Text != "") cmd.Parameters.AddWithValue("@替代用料", txt替代用料S.Text);
                SqlDataReader sqlreader = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                //dt.Columns.Add("項次");//add new 項次 column
                dt.Columns.Add("材編");
                dt.Columns.Add("品名");
                dt.Columns.Add("規格");
                dt.Columns.Add("現有庫存量");
                dt.Columns.Add("安全庫存量");
                //int k=1;
                while (sqlreader.Read())
                {
                    DataRow dtrows = dt.NewRow();
                    //dtrows["項次"]=k.ToString();
                    dtrows["材編"] = sqlreader["材編"];
                    dtrows["品名"] = sqlreader["品名"];
                    dtrows["規格"] = sqlreader["規格"];
                    dtrows["現有庫存量"] = sqlreader["現有庫存量"];
                    dtrows["安全庫存量"] = sqlreader["安全庫存量"];
                    dt.Rows.Add(dtrows);
                    //k++;
                }

                //change column width
                //dataGridview庫存.Columns[0].Width = 30;
                dataGridview庫存.DataSource = dt;
                PublicClass.setRowNumber(dataGridview庫存);
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }

        private void dataGridview庫存_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {

            PublicClass.setRowNumber(dataGridview庫存);
        }

        private void dataGridview庫存_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {

        }

        private void btnAll_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                txt品名S.ResetText();
                txt材編S.ResetText();
                txt規格S.ResetText();
                txt供應商S.ResetText();
                txt現有庫存量S.ResetText();
                txt安全庫存量S.ResetText();
                txt替代用料S.ResetText();
                con.Open();

                string sp = "sp_庫存_尋找";
                SqlCommand cmd = new SqlCommand(sp, con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlDataReader sqlreader = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                //dt.Columns.Add("項次");//add new 項次 column
                dt.Columns.Add("材編");
                dt.Columns.Add("品名");
                dt.Columns.Add("規格");
                dt.Columns.Add("現有庫存量");
                dt.Columns.Add("安全庫存量");
                //int k=1;
                while (sqlreader.Read())
                {
                    DataRow dtrows = dt.NewRow();
                    //dtrows["項次"]=k.ToString();
                    dtrows["材編"] = sqlreader["材編"];
                    dtrows["品名"] = sqlreader["品名"];
                    dtrows["規格"] = sqlreader["規格"];
                    dtrows["現有庫存量"] = sqlreader["現有庫存量"];
                    dtrows["安全庫存量"] = sqlreader["安全庫存量"];
                    dt.Rows.Add(dtrows);
                    //k++;
                }

                //change column width
                //dataGridview庫存.Columns[0].Width = 30;
                dataGridview庫存.DataSource = dt;
                PublicClass.setRowNumber(dataGridview庫存);
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }

        }

        private void btn_供應商_查詢_Click(object sender, EventArgs e)
        {
            frmSupplierSearch frm = new frmSupplierSearch(txt供應商碼);
            frm.ShowDialog();
        }

        private void btn_管理者_查詢_Click(object sender, EventArgs e)
        {
            frmManagerSearch frm = new frmManagerSearch(txt管理者代號);
            frm.ShowDialog();
        }

        private void btn_add_供應商_Click(object sender, EventArgs e)
        {
            //checkinput_供應商();
            if (!b庫存Insert)
            {
                MessageBox.Show("很抱歉，您沒有權限", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (txt供應商嗎_供應商.Text == "")
            {
                MessageBox.Show("供應商碼不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt供應商嗎_供應商.Focus();
                return;
            }
            if (txt供應商名稱_供應商.Text == "")
            {
                MessageBox.Show("供應商名稱不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt供應商名稱_供應商.Focus();
                return;
            }
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                string sp_insert = "sp_供應商_新增";
                SqlCommand cmd = new SqlCommand(sp_insert, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@供應商碼", txt供應商嗎_供應商.Text);
                cmd.Parameters.AddWithValue("@供應商名稱", txt供應商名稱_供應商.Text);
                cmd.Parameters.AddWithValue("@備註", txt備註_供應商.Text);
                cmd.ExecuteNonQuery();
                MessageBox.Show("新增成功");
                LoadAll_供應商();
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }

        private void btn_update_供應商_Click(object sender, EventArgs e)
        {
            //checkinput_供應商();
            if (!b庫存Edit)
            {
                MessageBox.Show("很抱歉,您沒有權限", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (txt供應商嗎_供應商.Text == "")
            {
                MessageBox.Show("供應商碼不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt供應商嗎_供應商.Focus();
                return;
            }
            if (txt供應商名稱_供應商.Text == "")
            {
                MessageBox.Show("供應商名稱不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt供應商名稱_供應商.Focus();
                return;
            }
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                string sp_update = "sp_供應商_更新";
                SqlCommand cmd = new SqlCommand(sp_update, con);
                cmd.CommandType = CommandType.StoredProcedure;
                int r = dataGV供應商_供應商.CurrentCell.RowIndex;
                string Id = dataGV供應商_供應商.Rows[r].Cells[0].Value.ToString();
                cmd.Parameters.AddWithValue("@供應商碼1", Id);
                cmd.Parameters.AddWithValue("@供應商碼", txt供應商嗎_供應商.Text);
                cmd.Parameters.AddWithValue("@供應商名稱", txt供應商名稱_供應商.Text);
                cmd.Parameters.AddWithValue("@備註", txt備註_供應商.Text);
                cmd.ExecuteNonQuery();
                MessageBox.Show("更新成功");
                LoadAll_供應商();
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }

        private void btn_del_供應商_Click(object sender, EventArgs e)
        {
            if (!b庫存Del)
            {
                MessageBox.Show("很抱歉,您沒有權限","錯誤",MessageBoxButtons.OK,MessageBoxIcon.Error);
                return;
            }
            SqlConnection con = new SqlConnection(connectionString);
            DialogResult dlg = MessageBox.Show("您確定要刪除這個供應商碼？", "警報", MessageBoxButtons.OKCancel,MessageBoxIcon.Question);
            if (dlg == DialogResult.OK)
            {
                try
                {
                    con.Open();
                    string sp_delete = "sp_供應商_刪除";
                    SqlCommand cmd = new SqlCommand(sp_delete, con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@供應商碼", txt供應商嗎_供應商.Text);
                    cmd.ExecuteNonQuery();
                    LoadAll_供應商();
                }
                catch (Exception ex)
                { MessageBox.Show(ex.Message); }
            }
        }
        void LoadAll_供應商()
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                string strcmd = "select * from 供應商";
                SqlDataAdapter cmd = new SqlDataAdapter(strcmd, con);
                cmd.SelectCommand.CommandType = CommandType.Text;
                DataTable dt = new DataTable();
                cmd.Fill(dt);
                dataGV供應商_供應商.DataSource = dt;
                PublicClass.setRowNumber(dataGV供應商_供應商);
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }

        private void dataGV供應商_供應商_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            int r = e.RowIndex;
            txt供應商嗎_供應商.Text = dataGV供應商_供應商.Rows[r].Cells[0].Value.ToString();
            txt供應商名稱_供應商.Text = dataGV供應商_供應商.Rows[r].Cells[1].Value.ToString();
            txt備註_供應商.Text = dataGV供應商_供應商.Rows[r].Cells[2].Value.ToString();
        }
        void checkinput_供應商()
        {
            if (txt供應商嗎_供應商.Text == "")
            {
                MessageBox.Show("供應商碼不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt供應商嗎_供應商.Focus();
                return;
            }
            if (txt供應商名稱_供應商.Text == "")
            {
                MessageBox.Show("供應商名稱不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt供應商名稱_供應商.Focus();
                return;
            }

        }
        private void 庫存管理_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void 庫存管理_tab_SelectedIndexChanged(object sender, EventArgs e)
        {
            //if (庫存管理_tab.SelectedIndex == 0)
            //{
            //    PublicClass.setRowNumber(dataGV供應商_供應商);
            ////    this.Size = new System.Drawing.Size(width, height);
            //}
            if (庫存管理_tab.SelectedIndex == 1)
            {
                PublicClass.setRowNumber(dataGV供應商_供應商);
                // this.Size = new System.Drawing.Size(width, height/2);
            }
            if (庫存管理_tab.SelectedIndex == 2)
            {
                PublicClass.setRowNumber(dataGV_管理者);
                //this.Size = new System.Drawing.Size(width, height / 2);
            }
            if (庫存管理_tab.SelectedIndex == 3)
            {
                PublicClass.setRowNumber(dataGV_替代用料);//dataGV_替代用料
                //this.Size = new System.Drawing.Size(width, height / 2);
            }


        }

        private void btn_add_管理者_Click(object sender, EventArgs e)
        {
            //checkinput_管理者();
            if (!b庫存Insert)
            {
                MessageBox.Show("很抱歉，您沒有權限", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (txt管理者代號_管理者.Text == "")
            {
                MessageBox.Show("管理者代號不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt管理者代號_管理者.Focus();
                return;
            }
            if (txt_姓名_管理者.Text == "")
            {
                MessageBox.Show("姓名不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt管理者代號_管理者.Focus();
                return;
            }
            if (txt電話_管理者.Text == "")
            {
                MessageBox.Show("電話不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt電話_管理者.Focus();
                return;
            }
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                string sp_insert = "sp_管理者_新增";
                SqlCommand cmd = new SqlCommand(sp_insert, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@管理者代號", txt管理者代號_管理者.Text);
                cmd.Parameters.AddWithValue("@姓名", txt_姓名_管理者.Text);
                cmd.Parameters.AddWithValue("@電話", txt電話_管理者.Text);
                cmd.ExecuteNonQuery();
                MessageBox.Show("新增成功");
                LoadAll_管理者();
                PublicClass.setRowNumber(dataGV_管理者);
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }

        void LoadAll_管理者()
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                string strcmd = "select * from 管理者";
                SqlDataAdapter cmd = new SqlDataAdapter(strcmd, con);
                cmd.SelectCommand.CommandType = CommandType.Text;
                DataTable dt = new DataTable();
                cmd.Fill(dt);
                dataGV_管理者.DataSource = dt;
                PublicClass.setRowNumber(dataGV_管理者);
                foreach (DataGridViewRow row in dataGV_管理者.Rows)
                {
                    //if(row. == DataControlRowType.DataRow)
                    if (row.Index == dataGV_管理者.Rows.Count - 1) break;
                    row.HeaderCell.Value = (row.Index + 1).ToString();
                    row.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                    //i++;
                }
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }

        private void btn_update_管理者_Click(object sender, EventArgs e)
        {
            //checkinput_管理者();
            if (!b庫存Edit)
            {
                MessageBox.Show("很抱歉，您沒有權限", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (txt管理者代號_管理者.Text == "")
            {
                MessageBox.Show("管理者代號不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt管理者代號_管理者.Focus();
                return;
            }
            if (txt_姓名_管理者.Text == "")
            {
                MessageBox.Show("姓名不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt管理者代號_管理者.Focus();
                return;
            }
            if (txt電話_管理者.Text == "")
            {
                MessageBox.Show("電話不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt電話_管理者.Focus();
                return;
            }
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                string sp_update = "sp_管理者_更新";
                SqlCommand cmd = new SqlCommand(sp_update, con);
                cmd.CommandType = CommandType.StoredProcedure;
                int r = dataGV_管理者.CurrentCell.RowIndex;
                string Id = dataGV_管理者.Rows[r].Cells[0].Value.ToString();
                cmd.Parameters.AddWithValue("@管理者代號1", Id);
                cmd.Parameters.AddWithValue("@管理者代號", txt管理者代號_管理者.Text);
                cmd.Parameters.AddWithValue("@姓名", txt_姓名_管理者.Text);
                cmd.Parameters.AddWithValue("@電話", txt電話_管理者.Text);
                cmd.ExecuteNonQuery();
                MessageBox.Show("更新成功");
                LoadAll_管理者();
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }

        private void btn_刪除_Click(object sender, EventArgs e)
        {
            if (!b庫存Del)
            {
                MessageBox.Show("很抱歉，您沒有權限", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            SqlConnection con = new SqlConnection(connectionString);
            DialogResult dlg = MessageBox.Show("您確定要刪除這個供應商碼？", "警報", MessageBoxButtons.OKCancel,MessageBoxIcon.Question);
            if (dlg == DialogResult.OK)
            {
                try
                {
                    con.Open();
                    string sp_delete = "sp_管理者_刪除";
                    SqlCommand cmd = new SqlCommand(sp_delete, con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@管理者代號", txt管理者代號_管理者.Text);
                    cmd.ExecuteNonQuery();
                    LoadAll_管理者();
                }
                catch (Exception ex)
                { MessageBox.Show(ex.Message); }
            }
        }

        private void dataGV_管理者_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            int r = e.RowIndex;
            txt管理者代號_管理者.Text = dataGV_管理者.Rows[r].Cells[0].Value.ToString();
            txt_姓名_管理者.Text = dataGV_管理者.Rows[r].Cells[1].Value.ToString();
            txt電話_管理者.Text = dataGV_管理者.Rows[r].Cells[2].Value.ToString();
        }
        void checkinput_管理者()
        {
            if (txt管理者代號_管理者.Text == "")
            {
                MessageBox.Show("管理者代號不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt管理者代號_管理者.Focus();
                return;
            }
            if (txt_姓名_管理者.Text == "")
            {
                MessageBox.Show("姓名不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt管理者代號_管理者.Focus();
                return;
            }
            if (txt電話_管理者.Text == "")
            {
                MessageBox.Show("電話不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt電話_管理者.Focus();
                return;
            }
        }

        private void btn_add_替代用料_Click(object sender, EventArgs e)
        {
            if (!b庫存Insert)
            {
                MessageBox.Show("很抱歉，您沒有權限", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            SqlConnection con = new SqlConnection(connectionString);
            if (txt材編_替代用料.Text == "")
            {
                MessageBox.Show("材編不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt材編_替代用料.Focus();
                return;
            }
            if (txt替代用料_替代用料.Text == "")
            {
                MessageBox.Show("替代用料不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt替代用料_替代用料.Focus();
                return;
            }
            try
            {
                con.Open();
                string sp_insert = "sp_替代用料_新增";
                SqlCommand cmd = new SqlCommand(sp_insert, con);
                cmd.CommandType = CommandType.StoredProcedure;
                //them vao @替代用料名稱
                cmd.Parameters.AddWithValue("@材編", txt材編_替代用料.Text);
                cmd.Parameters.AddWithValue("@替代用料碼", txt替代用料_替代用料.Text);
                string strcmd1 = string.Format("select 品名 from 產品 where 材編='{0}'", txt替代用料_替代用料.Text);
                SqlCommand cmd1 = new SqlCommand(strcmd1, con);
                string str = cmd1.ExecuteScalar().ToString();
                cmd.Parameters.AddWithValue("@替代用料名稱", str);
                cmd.ExecuteNonQuery();
                MessageBox.Show("新增成功");
                loadall_替代用料();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }
        void loadall_替代用料()
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                string strcmd = "select 材編,替代用料碼 from 替代用料";
                SqlDataAdapter cmd = new SqlDataAdapter(strcmd, con);
                cmd.SelectCommand.CommandType = CommandType.Text;
                DataTable dt = new DataTable();
                cmd.Fill(dt);
                dataGV_替代用料.DataSource = dt;
                PublicClass.setRowNumber(dataGV_替代用料);
                con.Close();

            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); con.Close(); }
        }

        private void btn_update_替代用料_Click(object sender, EventArgs e)
        {
            if (!b庫存Edit)
            {
                MessageBox.Show("很抱歉，您沒有權限", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            SqlConnection con = new SqlConnection(connectionString);
            if (txt材編_替代用料.Text == "")
            {
                MessageBox.Show("材編不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt材編_替代用料.Focus();
                return;
            }
            if (txt替代用料_替代用料.Text == "")
            {
                MessageBox.Show("替代用料不能放空", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txt替代用料_替代用料.Focus();
                return;
            }
            try
            {
                con.Open();
                string sp_insert = "sp_替代用料_更新";
                SqlCommand cmd = new SqlCommand(sp_insert, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@材編", txt材編_替代用料.Text);
                cmd.Parameters.AddWithValue("@替代用料碼", txt替代用料_替代用料.Text);
                string strcmd1 = string.Format("select 品名 from 產品 where 材編='{0}'", txt替代用料_替代用料.Text);
                SqlCommand cmd1 = new SqlCommand(strcmd1, con);
                string str = cmd1.ExecuteScalar().ToString();
                cmd.Parameters.AddWithValue("@替代用料名稱", str);
                int r = dataGV_替代用料.CurrentCell.RowIndex;
                string Id = dataGV_替代用料.Rows[r].Cells[0].Value.ToString();
                string Id1 = dataGV_替代用料.Rows[r].Cells[1].Value.ToString();
                cmd.Parameters.AddWithValue("@材編1", Id);
                cmd.Parameters.AddWithValue("@替代用料碼1", Id1);
                cmd.ExecuteNonQuery();
                MessageBox.Show("更新成功");
                loadall_替代用料();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }

        private void btn_del_替代用料_Click(object sender, EventArgs e)
        {
            if (!b庫存Del)
            {
                MessageBox.Show("很抱歉，您沒有權限", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            DialogResult dlg = MessageBox.Show("您確定要刪除這個項目", "警報", MessageBoxButtons.OKCancel,MessageBoxIcon.Question);
            if (dlg == DialogResult.OK)
            {
                SqlConnection con = new SqlConnection(connectionString);
                try
                {
                    con.Open();
                    string sp_delete = "sp_替代用料_刪除";
                    SqlCommand cmd = new SqlCommand(sp_delete, con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@替代用料碼", txt替代用料_替代用料.Text);
                    cmd.Parameters.AddWithValue("@材編", txt材編_替代用料.Text);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("刪除成功");
                    loadall_替代用料();
                }
                catch (Exception ex) { MessageBox.Show(ex.Message); }
            }
            else return;
        }

        private void 產品_Click(object sender, EventArgs e)
        {
            //MessageBox.Show("OK");
        }

        private void 庫存管理_tab_Selected(object sender, TabControlEventArgs e)
        {

        }

        private void 庫存管理_tab_SelectedTabChanged(object sender, TabControlEventArgs e)
        {

        }

        private void dataGV_替代用料_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            int r = e.RowIndex;
            txt材編_替代用料.Text = dataGV_替代用料.Rows[r].Cells[0].Value.ToString();
            txt替代用料_替代用料.Text = dataGV_替代用料.Rows[r].Cells[1].Value.ToString();
            // txt電話_管理者.Text = dataGV_管理者.Rows[r].Cells[2].Value.ToString();
        }

        private void btm_clear_Click(object sender, EventArgs e)
        {
            txt材編_替代用料.ResetText();
            txt替代用料_替代用料.ResetText();
        }

        private void btn_clear3_Click(object sender, EventArgs e)
        {
            txt管理者代號_管理者.ResetText();
            txt_姓名_管理者.ResetText();
            txt電話_管理者.ResetText();
        }

        private void btn_clear2_Click(object sender, EventArgs e)
        {
            txt供應商嗎_供應商.ResetText();
            txt供應商名稱_供應商.ResetText();
            txt備註_供應商.ResetText();
        }

        private void btn_替代用料_材編_查詢_Click(object sender, EventArgs e)
        {
            try
            {
                frmSearchProductID frm = new frmSearchProductID(txt材編_替代用料);
                frm.ShowDialog();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }

        private void btn_替代用料_查詢_Click(object sender, EventArgs e)
        {
            try
            {
                frmSearchProductID frm = new frmSearchProductID(txt替代用料_替代用料);
                frm.ShowDialog();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }

        private void dataGridview庫存_ColumnHeaderMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {

        }

        private void dataGridview庫存_ColumnHeaderMouseClick_1(object sender, DataGridViewCellMouseEventArgs e)
        {
            PublicClass.setRowNumber(dataGridview庫存);
        }

        private void dataGV_替代用料_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            PublicClass.setRowNumber(dataGV_替代用料);
        }

        private void dataGV_管理者_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            PublicClass.setRowNumber(dataGV_管理者);
        }

        private void dataGV供應商_供應商_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            PublicClass.setRowNumber(dataGV供應商_供應商);
        }

        private void txt管理者代號_TextChanged(object sender, EventArgs e)
        {
            //MessageBox.Show(txt管理者代號.Text);
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                string temp = "";
                string strcmd = String.Format("select 姓名 from 管理者 where 管理者代號=N'{0}'", txt管理者代號.Text);
                SqlCommand cmd = new SqlCommand(strcmd, con);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) temp = reader[0].ToString();
                //label10.Text = "";
                if (temp == "") label10.Text = "";
                else label10.Text = temp;
                con.Close();
            }
            catch (Exception ex) { }
        }

        private void btn_cancel_Click(object sender, EventArgs e)
        {
            this.Size = new System.Drawing.Size(this.Size.Width, this.Size.Height - gBAddNew.Height);
            gBAddNew.ResetText();
            txt品名.ResetText();
            txt材編.ResetText();
            txt規格.ResetText();
            txt單位數量.ResetText();
            txt安全庫存量.ResetText();
            txt供應商碼.ResetText();
            txt數量.ResetText();
            rtb備註.ResetText();
            txt管理者代號.ResetText(); 
            產品照片.Image = null;

            gBAddNew.Visible = true;
            txt加附.Enabled = true;
            txt材編.Enabled = true;
            txt規格.Enabled = true;
            txt品名.Enabled = true;
            txt單位數量.Enabled = true;
            //txtreplaceproductcode.Enabled = true;
            txt安全庫存量.Enabled = true;
            // txt現有庫存量.Enabled = true;
            //txtsupplier.Enabled = true;
            rtb備註.Enabled = true;
            chb停止供貨.Enabled = true;
            chb停止供貨.Checked = false;
        }

        private void btn_delAll_Click(object sender, EventArgs e)
        {
            if (!b庫存Del) btn_delAll.Enabled = false;
           
            SqlConnection con = new SqlConnection(connectionString);
            DialogResult dialogresult = MessageBox.Show("您確定要刪除這個產品嗎？", "訊息", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
            if (dialogresult == DialogResult.OK)
            {
                try
                {
                    con.Open();
                    int r = dataGridview庫存.CurrentCell.RowIndex;
                    string productcode = dataGridview庫存.Rows[r].Cells[0].Value.ToString();
                    string strcmd = String.Format("delete from 產品 where 材編=N'{0}'", productcode);
                    //delete from 替代用料 which 替代用料碼=productcode
                    string strcmd1 = String.Format("delete from 替代用料 where 替代用料碼=N'{0}'", productcode);
                    SqlCommand cmd = new SqlCommand(strcmd, con);
                    SqlCommand cmd1 = new SqlCommand(strcmd1, con);
                    cmd.ExecuteNonQuery();
                    cmd1.ExecuteNonQuery();
                    con.Close();
                    Searchby();
                    loadall_替代用料();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    con.Close();
                }
            }
            else return;
        }

        private void btn_refresh_Click(object sender, EventArgs e)
        {

        }

        private void bt_匯出_Click(object sender, EventArgs e)
        {
            try
            {
                if (!b庫存Search) bt_匯出.Enabled = false;
                Export_Excel excel = new Export_Excel();
                DataTable dt = (DataTable)dataGridview庫存.DataSource;
                excel.Export(dt, "Sheet1", "產品資訊");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}
